import os
import re
import time
import datetime
import sqlite3
from flask import g
from util import path

DATABASE = 'database.db'


def init_db():
    conn = sqlite3.connect(DATABASE)
    with open('database_init.sql', "r", encoding="utf-8") as f:
        conn.executescript(f.read())
    conn.commit()
    conn.close()


def get_db():
    try:
        db = getattr(g, '_database', None)
        if db is None:
            db = g._database = sqlite3.connect(os.path.join("util", DATABASE))
        return db
    except:
        db = sqlite3.connect(os.path.join("util", DATABASE))
        return db


def deal_insert(sql, db_conn=None):
    if db_conn is None:
        db_conn = get_db()
    cursor = db_conn.cursor()
    cursor.execute(sql)
    db_conn.commit()


def deal_select(sql, db_conn=None):
    if db_conn is None:
        db_conn = get_db()
    cursor = db_conn.cursor()
    cursor.execute(sql)
    rs = cursor.fetchall()
    return rs


def deal_insert_try(sql):
    try:
        deal_insert(sql)
        return True
    except Exception as e:
        return False


def deal_select_try(sql):
    try:
        return deal_select(sql)
    except Exception as e:
        return []


def create_session(session_info_json):
    user_id = session_info_json.get("user_id", 0)
    created_time = time.strftime('%Y-%m-%d %H:%M:%S')
    session_name = session_info_json.get("session_name")
    session_token = session_info_json.get("session_token")
    session_path = session_info_json.get("session_path")
    session_info = session_info_json.get("session_info", "会话信息")
    session_status = session_info_json.get("session_status", "会话状态")
    session_msg_tag = session_info_json.get("session_msg_tag", "0000")
    sql = f"INSERT INTO table_session " \
          f"(user_id, created_time, session_name, session_token, session_path, " \
          f"session_info, session_status, session_msg_tag) " \
          f"VALUES " \
          f"('{user_id}', '{created_time}', '{session_name}', '{session_token}', '{session_path}', " \
          f"'{session_info}', '{session_status}', '{session_msg_tag}')"
    return deal_insert_try(sql)


def update_session_msg_tag(session_token):
    session_msg_tag = path.get_file_name_random()
    sql = f"UPDATE table_session SET session_msg_tag='{session_msg_tag}' WHERE session_token='{session_token}'"
    deal_insert(sql)


def update_session_status(session_token, session_status):
    sql = f"UPDATE table_session SET session_status='{session_status}' WHERE session_token='{session_token}'"
    deal_insert(sql)
    update_session_msg_tag(session_token)


def update_session_info(session_token, session_info):
    sql = f"UPDATE table_session SET session_info='{session_info}' WHERE session_token='{session_token}'"
    deal_insert(sql)
    update_session_msg_tag(session_token)


def update_session_name(session_token, session_name):
    sql = f"UPDATE table_session SET session_name='{session_name}' WHERE session_token='{session_token}'"
    deal_insert(sql)
    update_session_msg_tag(session_token)


def get_session_msg_tag(session_token):
    sql = f"SELECT session_msg_tag FROM table_session WHERE session_token='{session_token}'"
    user_search = deal_select_try(sql)
    if len(user_search) > 0:
        return user_search[0][0]
    return 1


def get_session_name(session_token):
    sql = f"SELECT session_name FROM table_session WHERE session_token='{session_token}'"
    user_search = deal_select_try(sql)
    if len(user_search) > 0:
        return user_search[0][0]
    return "新建会话"


def get_session_path(session_token):
    sql = f"SELECT session_path FROM table_session WHERE session_token='{session_token}'"
    user_search = deal_select_try(sql)
    if len(user_search) > 0:
        return user_search[0][0]
    return ""


def session_delete(session_token):
    sql = f"DELETE FROM table_session WHERE session_token='{session_token}'"
    deal_insert_try(sql)


def get_session_list(user_token):
    # TODO 根据user_token进行过滤
    sql = f"SELECT * FROM table_session"
    user_search = deal_select_try(sql)
    return user_search


def get_user_id_by_username(username):
    sql = f"SELECT id FROM table_user WHERE username='{username}'"
    user_search = deal_select_try(sql)
    if len(user_search) > 0:
        return user_search[0][0]
    return 1


def is_user_login(username, user_token):
    if username == "guest" or not re.match("^[0-9]{18}$", user_token):
        return False
    sql = f"SELECT * FROM table_user, table_token WHERE table_user.id=table_token.user_id AND table_user.username='{username}' AND table_token.token='{user_token}'"
    user_search = deal_select_try(sql)
    if len(user_search) > 0:
        return True
    return False


def add_user_in_group_by_id(user_id, group_id):
    sql = f"INSERT INTO table_group_user(user_id, group_id) VALUES('{user_id}', '{group_id}')"
    return deal_insert_try(sql)


def del_file_by_file_token(file_token):
    sql = f"DELETE FROM table_files WHERE file_token = '{file_token}'"
    return deal_insert_try(sql)


if __name__ == '__main__':
    init_db()
